package com.gxuwz.attend.dao;

import com.gxuwz.attend.entity.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    /**
     * 添加学生数据
     * @param stu
     */
    public void insertStudent(Student stu){
        String sql = "insert into sys_student (stuID,classID,stuName,sex,address,stuTel,contact,contactTel)values(?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,stu.getStuID(),stu.getClassID(),stu.getStuName(),stu.getSex(),stu.getAddress(),stu.getStuTel(),stu.getContact(),stu.getContactTel());
    }
    /**
     * 删除学生数据
     * @param stuID
     */
    public void deleteStudent(String stuID){
        String sql = "delete from sys_student where stuID=?";
        jdbcTemplate.update(sql,stuID);
    }

    /**
     * 更新学生数据
     * @param stu
     */
    public void updateStudent(Student stu){
        String sql = "update sys_student set classID=?,stuName=?,sex=?,address=?,stuTel=?,contact=?,contactTel=? where stuID=?";
        jdbcTemplate.update(sql,stu.getClassID(),stu.getStuName(),stu.getSex(),stu.getAddress(),stu.getStuTel(),stu.getContact(),stu.getContactTel(),stu.getStuID());
    }
    /**
     * 查询全部学生数据
     * @return
     */
    public List<Student> queryStudents(){
        String sql = "select * from sys_student where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Student> studentList = new ArrayList<>();

        studentList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                String stuID = resultSet.getString("stuID");
                String classID = resultSet.getString("classID");
                String stuName = resultSet.getString("stuName");
                String sex = resultSet.getString("sex");
                String address = resultSet.getString("address");
                String stuTel = resultSet.getString("stuTel");
                String contact = resultSet.getString("contact");
                String contactTel = resultSet.getString("contactTel");
                student.setStuID(stuID);
                student.setClassID(classID);
                student.setStuName(stuName);
                student.setSex(sex);
                student.setAddress(address);
                student.setStuTel(stuTel);
                student.setContact(contact);
                student.setContactTel(contactTel);
                return student;
            }
        });
        return studentList;
    }
    /**
     * 模糊查询学生数据
     * @param stu
     * @return
     */
    public List<Student> queryStudent(Student stu){
        String sql = "select * from sys_student where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Student> studentList = new ArrayList<>();
        if(stu!=null){
            if (stu.getStuID()!=null&&!"".equals(stu.getStuID().trim())){
                sql += " and stuID=?";
                paramValues.add(stu.getStuID());
            }
            if (stu.getClassID()!=null&&!"".equals(stu.getClassID().trim())){
                sql += " and classID=?";
                paramValues.add(stu.getClassID());
            }
            if (stu.getSex()!=null&&!"".equals(stu.getSex().trim())){
                sql += " and sex=?";
                paramValues.add(stu.getSex());
            }
            if (stu.getAddress()!=null&&!"".equals(stu.getAddress().trim())){
                sql += " and address=?";
                paramValues.add(stu.getAddress());
            }
            if (stu.getStuTel()!=null&&!"".equals(stu.getStuTel().trim())){
                sql += " and stuTel=?";
                paramValues.add(stu.getStuTel());
            }
            if (stu.getContact()!=null&&!"".equals(stu.getContact().trim())){
                sql += " and contact=?";
                paramValues.add(stu.getContact());
            }
            if (stu.getContactTel()!=null&&!"".equals(stu.getContactTel().trim())){
                sql += " and contactTel=?";
                paramValues.add(stu.getContactTel());
            }
            if (stu.getStuName()!=null&&!"".equals(stu.getStuName().trim())){
                sql += " and stuName like ?";
                paramValues.add(stu.getStuName()+"%");
            }
        }
        studentList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                String stuID = resultSet.getString("stuID");
                String classID = resultSet.getString("classID");
                String stuName = resultSet.getString("stuName");
                String sex = resultSet.getString("sex");
                String address = resultSet.getString("address");
                String stuTel = resultSet.getString("stuTel");
                String contact = resultSet.getString("contact");
                String contactTel = resultSet.getString("contactTel");
                student.setStuID(stuID);
                student.setClassID(classID);
                student.setStuName(stuName);
                student.setSex(sex);
                student.setAddress(address);
                student.setStuTel(stuTel);
                student.setContact(contact);
                student.setContactTel(contactTel);
                return student;
            }
        });
        return studentList;
    }
}
